*WHAT'S ACROSS THE BORDER?
*JHA, NEUMARK, AND RODRIGUEZ-LOPEZ

/*
THIS FILE CREATES THE MASTER SAMPLE AT THE COUNTY-20IND LEVEL.
*/

include directories_build.do


* Load data at the county-industry level
use "`cbpdir'`s'cbp_county_state_wages_20ind_1990_2016.dta", clear


* Label variables
label var year "1990-2011"
label var est "Number of establishments"
label var emp "Employment"
label var qp1 "Nominal first quarter payroll"
label var ap "Nominal annual payroll"
label var sic87xx "20 industries"
label var sic87xx_desc "20 industries description"
label var czone "AADHP commuting zones"
label var cty_fips "County FIPS"
label var totpop "Total population county"
label var workagepop "Total working age population county"
order year czone state sic87xx emp ap qp1 est totpop workagepop

* Merge minimum wage data
ren state statefips
merge m:1 statefips year using "`mwdir'`s'VZ_state_annual.dta"
drop if _merge==2
drop _merge
ren statefips state 

* Merge ranking data
merge m:1 sic87xx using "`cbpdir'`s'cbp_sic87xx_wagerank.dta"  
drop _merge

foreach b in "emp" "ap" "qp1" "est" {

sort cty_fips year 

by cty_fips year: egen tot`b'=total(`b')
}


* Construct nominal and real wages


	*Calculate (average) hourly wage variables
	gen wageap=ap/(2087*emp)*1000
	gen wageqp=qp1/(521.75*emp)*1000
	gen totwageap=totap/(2087*totemp)*1000
	gen totwageqp=totqp1/(521.75*totemp)*1000
	
	*Keep values for rest of country
	gen totwageapm=(totap-ap)/(2087*(totemp-emp))*1000
	gen totwageqpm=(totqp1-qp1)/(521.75*(totemp-emp))*1000


label var totest "Number of establishments (county)"
label var totemp "Employment (county)"
label var totqp1 "Nominal first quarter payroll (county)"
label var totap "Nominal annual payroll (county)"
label var wageap "Nominal hourly wage using AP (county-ind)"
label var wageqp "Nominal hourly wage using QP (county-ind)"
label var totwageap "Nominal hourly wage using AP (county)"
label var totwageqp "Nominal hourly wage using QP (county)"
label var totwageapm "Nominal hourly wage using AP MINUS (county)"
label var totwageqpm "Nominal hourly wage using QP MINUS (county)"
label var state "State FIPS"
label var multi "Multi-state czone indicator"
label var wagerank90 "Wage ranking of industry in 1990"


replace multi=0 if multi==.
gen multi_two=multi
replace multi_two=0 if three==1


compress
	
save "`datadir'`s'cbp_county_state_20ind_sample.dta", replace

